package guest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

public class Guest {

	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE"; 
	String db_user = "hr";
	String db_pwd = "hr";

	Connection conn;
	
	GuestVo vo;

	public Guest(){
		try {
			// 1) 드라이버 로딩
			Class.forName(driver).newInstance();
			
			// 2) Connection 객체 생성
			conn = DriverManager.getConnection(url, db_user, db_pwd);
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
	/*
	 *  방명록에서 불러내기 위한 함수
	 */
	public Vector<GuestVo> list(){
		Vector<GuestVo> v = new Vector<>();
		
		String sql = "select * from guest where mid like ? or content like ? or subject like ?";
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, "%" + vo.getFind() + "%");
			ps.setString(2, "%" + vo.getFind() + "%");
			ps.setString(3, "%" + vo.getFind() + "%");
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				GuestVo gv = new GuestVo();
				gv.setSerial(rs.getInt("serial"));
				gv.setMid(rs.getString("mid"));
				gv.setSubject(rs.getString("subject"));
				gv.setContent(rs.getString("content"));
				gv.setMdate(rs.getDate("mdate"));
				
				v.add(gv);
			}
			rs.close();
			ps.close();
			conn.close();
		
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return v;
	}
	
	// 글 등록 창에서 글을 등록하는 함수
	public boolean insert(){
		boolean flag = false;
		String sql = "insert into guest(serial, mid, subject, content) values(serial.nextval, ?, ? ,?)";
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getMid());
			ps.setString(2, vo.getSubject());
			ps.setString(3, vo.getContent());
			
			/*
			 *  executeUpdate() 는 입력, 삭제, 수정에서 사용 - INSERT, UPDATE, DELETE 문
			 *  또한, executeUpdate() 에서 성공적으로 쿼리문이 실행이 되면 '1'값이 반환된다.
			 *  executeQuery() 는 조회 할때 사용 - SELECT 문
			 *  그리고 SQL 이 이상이 있는지 없는지 확인하기 위해서 ResultSet 을 사용해준다.
			 */
			
			int cnt = ps.executeUpdate();
			if(cnt>0) flag = true;
			
			ps.close();
			conn.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return flag;
	}
	
	public GuestVo view(){
		GuestVo v = new GuestVo();
		
		String sql = "select * from guest where serial=?";
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()){
				v.setSerial(rs.getInt("serial"));
				v.setMid(rs.getString("mid"));
				v.setSubject(rs.getString("subject"));
				v.setContent(rs.getString("content"));
				v.setMdate(rs.getDate("mdate"));
			}
			rs.close();
			ps.close();
			conn.close();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return v;
	}
	
	// 게시판의 글을 수정하기 위한 함수
	public boolean modify(){
		boolean flag = false;
		
		String sql = "update guest set subject=?, content=? where serial=?";
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getSubject());
			ps.setString(2, vo.getContent());
			ps.setInt(3, vo.getSerial());
			
			int cnt = ps.executeUpdate();
			if(cnt>0) flag= true;
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return flag;
	}
	
	// 해당 게시판의 글을 삭제하기 위한 함수
	public boolean delete(){
		boolean flag = false;
		
		String sql = "delete from guest where serial=?";
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			int cnt = ps.executeUpdate();
			if(cnt>0) flag = true;
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return flag;
	}
	
	// GuestVo 에다가 값을 넣기 위한 조치
	public GuestVo getVo() {
		return vo;
	}

	public void setVo(GuestVo vo) {
		this.vo = vo;
	}
}
